package com.wnzt.baszh.dao.impl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.wnzt.baszh.dao.IMedicalCheckDao;
import com.wnzt.baszh.entity.SzhCxmbtjmx;
import com.wnzt.baszh.entity.Tsysuser;
import com.wnzt.baszh.util.common.ConstantsUtils;
import com.wnzt.baszh.util.common.GlobalFun;
import com.wnzt.baszh.util.common.ObjectMapper;
import com.wnzt.baszh.util.common.PageHelper;
import com.wnzt.baszh.util.common.PageHelperService;

/**
 * 病案查询管理  dao层实现类
 */

@Repository
public class MedicalCheckDaoImpl extends PageHelperService implements IMedicalCheckDao {
	public static final  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
	
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    @Autowired
	private SessionFactory sessionFactory;
    
    @Override
    public Object loadDoctorByDocType(String code) {
        String codebh = code+"BH";
        
        //判断为主任医生的话
        if ("FZRDOCTOR".equals(code)) {
            codebh = "FZRDOCTBH";
        }
        String querySql = "select distinct "+codebh+" id,"+code+" text from szh_base where "+code+" is not null and "+code+" <>''";
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object editConditionTempName(String name, String mbID) {
        boolean b = true;
        String updateSql = "update SZH_CXMBMAIN set FMBMC = '"+name+"' where fid="+mbID;
        try {
            jdbcTemplate.execute(updateSql);
        } catch (Exception e) {
            b = false;
        }
        
        return b;
    }

    @Override
    public Object addConditionTemp(Map<String, Object> map) {
        
        //1、建立模板  将模板数据插入模板表中  并返回该数据的主键  ID
        String insertMainSql = "insert into SZH_CXMBMAIN (FMBMC,FUSERCODE) VALUES('"+map.get("TempMC")+"','"+map.get("fcode")+"') select @@identity";
        
        int b = 0;  //获取新模板ID值
        try {
            b = this.jdbcTemplate.queryForObject(insertMainSql, Integer.class);
            
            //删除map中 TempMC键值对
            map.remove("TempMC");
            
            //将数据插入条件明细表中,同时更新主表fsql字段值
            insertQueryCondition(map, b);
            
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    public Object editConditionTemp(Map<String, Object> map) {
 
        int TempID = Integer.parseInt(map.get("TempID").toString());
        
        //先删除该模板下的条件明细
        String deleteSql = "delete from SZH_CXMBDETAIL where FCXMBID ="+TempID;
        
        try {
            jdbcTemplate.execute(deleteSql);
            
            //删除map中 TempID键值对
            map.remove("TempID");
            
            //将数据插入条件明细表中,同时更新主表fsql字段值
            insertQueryCondition(map, TempID);
            
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    @Override
    public Object deleteConditionTemp(String mbID) {
        
        boolean b = true;
        //删除模板表中记录
        String deleteMainSql = "delete from SZH_CXMBMAIN where fid="+mbID;
        
        //删除模板明细表中的记录
        String deleteDetailSql = "delete from SZH_CXMBDETAIL where FCXMBID ="+mbID;
        
        
        //删除条件组
        String conditionGroupSql="delete from SZH_CXMBTJZ where fmbid="+mbID;
        
        //删除条件
        String conditionSql="delete from SZH_CXMBTJMX where fzid in (select fid from SZH_CXMBTJZ where fmbid="+mbID+") ";
        try {
            jdbcTemplate.execute(deleteMainSql);
            jdbcTemplate.execute(deleteDetailSql);
            jdbcTemplate.execute(conditionSql);//删除条件
            jdbcTemplate.execute(conditionGroupSql);//删除条件组
        } catch (Exception e) {
            b = false;
        }
        return b;
    }
    
    /**
     * 根据条件模板id添加条件明细到条件明细表中,同时组成sql语句  更新条件模板表中 fsql字段值
     */
    public void insertQueryCondition(Map<String, Object> map, int mbID) {
        
        //调用返回sql语句方法
        String sql = returnQuerySql(map, mbID, 0);
        
        //更新主表中的fsql数据
        String updateSql = "update SZH_CXMBMAIN set fsql = '"+sql+"' where fid="+mbID;
        
        try {
            this.jdbcTemplate.execute(updateSql);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public Object loadConditionTemple(String fcode,String ftype) {
    	ftype=ftype==null?"1":ftype;
        String querySql = "SELECT FID id,FMBMC text FROM SZH_CXMBMAIN WHERE FUSERCODE='"+fcode+"' AND FTYPE="+ftype;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object loadConditionByTemple(String mbID) {
        String querySql = "select * from SZH_CXMBDETAIL where FCXMBID="+mbID;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }

    @Override
    public Object loadFieldsByTempId(String mbID) {
        String querySql = "select FID, FFIELDCODE field,FFIELDNAME title,ALIGN align,WIDTH width from SZH_FIELDMBDETAIL where FFIELDMBID="+mbID;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }

    @Override
    public List<Map<String, Object>> SubmitConditionCheck(Map<String, Object> map) {
        
        //调用返回sql语句方法
        String sql = returnQuerySql(map, 0, 1);
        
        //执行该sql语句  同时返回查询出的结果集
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList("select * from ("+sql+") TT order by tt.fzt desc");
        return list;
    }
    
    //创建一个返回查询sql语句方法(type 0:添加或修改模板方法返回的sql语句  1:表示点击查询按钮返回的sql语句)
    public String  returnQuerySql(Map<String, Object> map, int mbID, int type) {
        
        String checkCydetkh = (String) map.get("checkCydetkh");  //出院科室科号拼接字符串  
        String checkDocbh = (String) map.get("checkDocbh");  //医生或护士工号拼接字符串
        
        String FRQLX = (String) map.get("FRQLX");            //日期类型(出院日期、入院日期)
        String FQSRQ = (String) map.get("FQSRQ");            //起始日期() 
        String FJSRQ = (String) map.get("FJSRQ");            //结束日期() 
        String FSEXLX = (String) map.get("FSEXLX");          //性别类型(1、全部  2、男 3、女) 
        
        String FQSAGE = (String) map.get("FQSAGE");          //起始年龄值()
        String FJSAGE = (String) map.get("FJSAGE");          //结束年龄值()
        String FAGELX = (String) map.get("FAGELX");          //年龄类型(1、年 2、月)
        
        String FPRNTEXT = (String) map.get("FPRNTEXT");      //病案号值
        String FNAMETEXT = (String) map.get("FNAMETEXT");    //姓名值
        String FDOCLX = (String) map.get("FDOCLX");          //医生类型值 
        
        String FZDBMMC = (String) map.get("FZDBMMC");        //诊断编码或名称(FICDM、诊断编码 FJBNAME、诊断名称)
        String FZDBMMCVAL = (String) map.get("FZDBMMCVAL");  //诊断编码或名称值
        String FZDLX = (String) map.get("FZDLX");            //诊断类型
        
        String FSSBMMC = (String) map.get("FSSBMMC");        //手术编码或名称(FOPCODE、手术编码 FOP、手术名称)
        String FSSBMMCVAL = (String) map.get("FSSBMMCVAL");  //手术编码或名称值
        String FSSLX = (String) map.get("FSSLX");            //手术类型(3、全部 0、主要手术 1、其他手术)
        
        //拼接条件语句
        String check = "";
        
        //获取用户工号
        String fcode = (String) map.get("fcode");
        
        //表示添加或修改  查询条件模板方法,执行插入语句,否则不执行以下代码
        if (type == 0) {
            //插入日期字段值
            String insertSql1 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'"+FRQLX+"','"+FRQLX+"','"+FQSRQ+","+FJSRQ+"')";
            
            //插入性别字段值
            String insertSql2 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FSEXBH','FSEXBH',"+FSEXLX+")";
            
            //插入年龄字段值
            String insertSql3 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FAGE','FAGE','"+FQSAGE+","+FJSAGE+","+FAGELX+"')";
            
            //插入病案号字段值
            String insertSql4 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FPRN','FPRN','"+FPRNTEXT+"')";
            
            //插入姓名字段值
            String insertSql5 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FNAME','FNAME','"+FNAMETEXT+"')";
            
            //插入医生类型对应值
            String insertSql6 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'"+FDOCLX+"','"+FDOCLX+"','"+checkDocbh.replace("''", "")+"')";
            
            //插入出院科室对应值
            String insertSql7 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FCYDEPT','FCYDEPT','"+checkCydetkh.replace("''", "")+"')";
            
            //插入诊断编码或诊断名称值
            String insertSql8 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'"+FZDBMMC+"','"+FZDBMMC+"','"+FZDBMMCVAL+"')";
            
            //插入手术编码或诊断名称值
            String insertSql9 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'"+FSSBMMC+"','"+FSSBMMC+"','"+FSSBMMCVAL+"')";
            
            //插入诊断类型值
            String insertSql10 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FZDLX','FZDLX','"+FZDLX+"')";
            
            //插入手术类型值
            String insertSql11 = "insert into SZH_CXMBDETAIL (FCXMBID, FFIELDCODE, FFIELDNAME, FFIELDVAL) values("+mbID+","
                    + "'FIFFSOP','FIFFSOP','"+FSSLX+"')";
            
            //执行插入语句
            jdbcTemplate.execute(insertSql1);
            jdbcTemplate.execute(insertSql2);
            jdbcTemplate.execute(insertSql3);
            jdbcTemplate.execute(insertSql4);
            jdbcTemplate.execute(insertSql5);
            jdbcTemplate.execute(insertSql6);
            jdbcTemplate.execute(insertSql7);
            jdbcTemplate.execute(insertSql8);
            jdbcTemplate.execute(insertSql9);
            jdbcTemplate.execute(insertSql10);
            jdbcTemplate.execute(insertSql11);
            
            check = "select fzyid FROM szh_dgsq where fusercode=''"+fcode+"'' and fshztbh = 1";
        }
        
        //住院id拼接字符串
        String zyids = "";
        String ztSQL="";
        //表示执行查询的方法
        if (type == 1) {
        	int qxtype=1;
        	//病案浏览权限  1.无权限  每份病历都需要申请  2.本科权限  除本科室外，所有的病历需要申请  3.全部权限  不需要申请
        	String sql="SELECT MAX(FLLQXTYPE) qxtype FROM SZH_GROUPLLQXINFO WHERE FGROUPCODE IN (SELECT FGROUPCODE FROM TSYSUSER WHERE FCODE='"+fcode+"')";
        	try{  
        		Map<String,Object> qx=this.jdbcTemplate.queryForMap(sql);
        		qxtype=(Integer) qx.get("qxtype");
            }catch (EmptyResultDataAccessException e) {  
            	qxtype=1;//没有设置默认无权限
            }
        	//获取当前日期
            String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
            //获取用户姓名
            String fname = (String) map.get("fname");
            //list存放具有浏览权限的病案住院id
            List<Map<String, Object>> list = null;
            //查看单个申请和图片分类申请对比当前日期比较判断 病案是否具有浏览权限
            String queryBapply = "SELECT a.FZYID FROM SZH_DGSQ a INNER JOIN(SELECT FSQID,FJSRQ FROM SZH_DGSQJL "
                    + "WHERE FSPZT='批准' and fjsrq>'"+date+"') b ON a.FID = b.FSQID and a.fusercode = '"+fcode+"'";
            list = jdbcTemplate.queryForList(queryBapply);
            //遍历mblist数组 将模板id放入 arra数组中
            for (int i = 0; i < list.size(); i++) {
                zyids += list.get(i).get("FZYID") + ",";
            }
            //查看批量申请批准，病案是否具有浏览权限
            String querybDefault = "SELECT * FROM SZH_PLSQJL WHERE FSPZT='批准' AND FJSRQ>'"+date+"'";
            List<Map<String, Object>> listabDefalut = jdbcTemplate.queryForList(querybDefault);
            //根据当前日期与批量申请 批准后的结束日期判断已经存在批量申请批准的记录
            if (listabDefalut.size()>0) {
                for (int i = 0; i < listabDefalut.size(); i++) {
                    //根据住院id 判断该病案是否在批量申请  sql语句中
                    String queryInfoByZyid = "SELECT fsqtjsql FROM SZH_PLSQ WHERE FID = "+listabDefalut.get(i).get("FSQID")+" "
                            + "AND FJYSQR = '"+fname+"' ";
                    //得到查询sql语句
                    String plsqCxSql = jdbcTemplate.queryForObject(queryInfoByZyid, String.class);
                    //将每个查询语句查询出的结果放置
                    List<Map<String, Object>> resuList = jdbcTemplate.queryForList(plsqCxSql);
                    if (resuList.size() > 0) {
                        //循环将住院id放入字符串中
                        for (int j = 0; j < resuList.size(); j++) {
                            zyids += resuList.get(i).get("FZYID");
                        }
                    }
                }
            }
            check = zyids.length()>1?zyids.substring(0, zyids.length()-1):zyids;
        	if(1==qxtype){//1.无权限  每份病历都需要申请 
                ztSQL="CASE when  t.fzyid IN("+check+") THEN 2 WHEN t.fzyid IN(select fzyid FROM SZH_SHOOTRECORD) THEN 1 else 0 END fzt ";
        	}else if(2==qxtype){// 2.本科权限  除本科室外，所有的病历需要申请  可浏览：本院已翻拍、本院已归档+申请的  
        		ztSQL="CASE when  t.fzyid IN("+check+") OR t.FZYID IN (SELECT FZYID FROM SZH_SHOOTRECORD WHERE FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FRYTYKH IN (SELECT (FTYKH+','+ISNULL(FCHILDREN,'''')) FTYKH FROM TWORKROOM WHERE FTYKH IN (SELECT FTYKH FROM TSYSUSER WHERE FCODE=''"+fcode.trim()+"'')))) OR t.FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FRYTYKH IN (SELECT (FTYKH+','+ISNULL(FCHILDREN,'''')) FTYKH FROM TWORKROOM WHERE FTYKH IN (SELECT FTYKH FROM TSYSUSER WHERE FCODE=''"+fcode.trim()+"'')))) THEN 2 WHEN t.fzyid IN(select fzyid FROM SZH_SHOOTRECORD) THEN 1 else 0 END fzt ";
        	}else if(3==qxtype){//3.全部权限  不需要申请(可浏览：已归档、已翻拍)
        		ztSQL="CASE WHEN t.FZYID IN (SELECT FZYID FROM SZH_SHOOTRECORD) OR t.FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FSTATUS=1) THEN 2 else 0 END fzt ";
        	}
            
        }
        
        //fcode
       
        
        //拼接sql语句
        String table = "select DISTINCT q.fid id,t.*,"+ztSQL+" from szh_base t ";
        if (ConstantsUtils.isNotBank(FZDBMMCVAL) || ConstantsUtils.isNotBank(FZDLX)) {
            table += "inner join TDIAGNOSE a on t.fprn=a.fprn and t.ftimes=a.ftimes ";
        }
        
        if (ConstantsUtils.isNotBank(FSSBMMCVAL) || ConstantsUtils.isNotBank(FSSLX)) {
            table += "inner join TOPERATION b on t.fprn=b.fprn and t.ftimes=b.ftimes ";
        }
        
        //拼接 申请主表
        table += "LEFT JOIN szh_dgsq q ON t.fzyid = q.fzyid AND q.fshztbh = 1 AND ftpflbh=0 ";
        
        StringBuffer  whereSql = new StringBuffer(" where 1=1");
        
        //起始日期
        if (ConstantsUtils.isNotBank(FQSRQ)) {
            whereSql.append(" and t."+FRQLX+">=''"+FQSRQ+"''");
        }
        
        //结束日期
        if (ConstantsUtils.isNotBank(FJSRQ)) {
            whereSql.append(" and t."+FRQLX+"<=''"+FJSRQ+"''");
        }
        
        //性别
        if ("1".equals(FSEXLX) || "2".equals(FSEXLX)) {
            whereSql.append(" and t.fsexbh="+FSEXLX);
        }
        
        //判断年龄单位类型为年
        if ("1".equals(FAGELX)) {
            if (ConstantsUtils.isNotBank(FQSAGE)) {
                whereSql.append(" and t.age>=''Y"+FSEXLX+"''");
            }else if (ConstantsUtils.isNotBank(FJSAGE)) {
                whereSql.append(" and t.age<=''Y"+FJSAGE+"''");
            }
        }else{
            if (ConstantsUtils.isNotBank(FQSAGE)) {
                whereSql.append(" and t.age>=''M"+FSEXLX+"''");
            }else if (ConstantsUtils.isNotBank(FJSAGE)) {
                whereSql.append(" and t.age<=''M"+FJSAGE+"''");
            } 
        }
        
        //判断如果病案号值不为空
        if (ConstantsUtils.isNotBank(FPRNTEXT)) {
            String valSql = "";
            
            String fprnArray [] = FPRNTEXT.split(",");
            
            for (int i = 0; i < fprnArray.length; i++) {
                
                //判断分割后的病案号值中存在%
                if (fprnArray[i].indexOf('%')>-1) {
                    
                    if (i == fprnArray.length-1) {
                        valSql += " t.fprn like ''"+fprnArray[i]+"''";
                        break;
                    }
                    valSql += " t.fprn like ''"+fprnArray[i]+"'' or";
                    
                }else {
                    if (i == fprnArray.length-1) {
                        valSql += " t.fprn = ''"+fprnArray[i]+"''";
                        break;
                    }
                    valSql += " t.fprn = ''"+fprnArray[i]+"'' or";
                }
            }
            String fprnSql = " and ("+valSql+")";
            whereSql.append(fprnSql);
        }
        
        
        //判断如果姓名值不为空
        if (ConstantsUtils.isNotBank(FNAMETEXT)) {
            String valSql = "";
            
            String fnameArray [] = FNAMETEXT.split(",");
            
            for (int i = 0; i < fnameArray.length; i++) {
                
                //判断分割后的病案号值中存在%
                if (fnameArray[i].indexOf('%')>-1) {
                    
                    if (i == fnameArray.length-1) {
                        valSql += " t.fname like ''"+fnameArray[i]+"''";
                        break;
                    }
                    valSql += " t.fname like ''"+fnameArray[i]+"'' or";
                    
                }else {
                    if (i == fnameArray.length-1) {
                        valSql += " t.fname = ''"+fnameArray[i]+"''";
                        break;
                    }
                    valSql += " t.fname = ''"+fnameArray[i]+"'' or";
                }
            }
            String fnameSql = " and ("+valSql+")";
            whereSql.append(fnameSql);
        }
        
        //判断如果诊断编码或名称不为空
        if (ConstantsUtils.isNotBank(FZDBMMCVAL)) {
            String valSql = "";
            
            String fzdbmmcArray [] = FZDBMMCVAL.split(",");
            
            for (int i = 0; i < fzdbmmcArray.length; i++) {
                
                //判断分割后的病案号值中存在%
                if (fzdbmmcArray[i].indexOf('%')>-1) {
                    
                    if (i == fzdbmmcArray.length-1) {
                        valSql += " a."+FZDBMMC+" like ''"+fzdbmmcArray[i]+"''";
                        break;
                    }
                    valSql += " a."+FZDBMMC+" like ''"+fzdbmmcArray[i]+"'' or";
                    
                }else {
                    if (i == fzdbmmcArray.length-1) {
                        valSql += " a."+FZDBMMC+" = ''"+fzdbmmcArray[i]+"''";
                        break;
                    }
                    valSql += " a."+FZDBMMC+" = ''"+fzdbmmcArray[i]+"'' or";
                }
            }
            String fzdSql = " and ("+valSql+")";
            whereSql.append(fzdSql);
        }
        
        //判断手术编码或名称不为空
        if (ConstantsUtils.isNotBank(FSSBMMCVAL)) {
            String valSql = "";
            
            String fssbmmcArray [] = FSSBMMCVAL.split(",");
            
            for (int i = 0; i < fssbmmcArray.length; i++) {
                
                //判断分割后的病案号值中存在%
                if (fssbmmcArray[i].indexOf('%')>-1) {
                    
                    if (i == fssbmmcArray.length-1) {
                        valSql += "  a."+FSSBMMC+" like ''"+fssbmmcArray[i]+"''";
                        break;
                    }
                    valSql += " a."+FSSBMMC+" like ''"+fssbmmcArray[i]+"'' or";
                    
                }else {
                    if (i == fssbmmcArray.length-1) {
                        valSql += " a."+FSSBMMC+" = ''"+fssbmmcArray[i]+"''";
                        break;
                    }
                    valSql += " a."+FSSBMMC+" = ''"+fssbmmcArray[i]+"'' or";
                }
            }
            String fssSql = " and ("+valSql+")";
            whereSql.append(fssSql);
        }
        
        //判断诊断类型不为空
        if (ConstantsUtils.isNotBank(FZDLX)) {
            //全部出院诊断
            if ("0".equals(FZDLX)) {
                whereSql.append(" and a.fzdlx in(''1'',''2'',''s'',''11'',''12'',''13'')");
                
                //病理诊断
            }else if ("5".equals(FZDLX)) {
                whereSql.append(" and t.FPHZDBH is not null");
                
                //中医诊断
            }else if ("6".equals(FZDLX)) {
                whereSql.append(" and a.fzdlx in(''11'',''12'',''13'')");
            }else {
                whereSql.append(" and a.fzdlx=''"+FZDLX+"''");
            }
        }
        
        //判断手术类型不为空
        if (ConstantsUtils.isNotBank(FSSLX)) {
            //全部手术类型
            if ("3".equals(FSSLX)) {
                whereSql.append("");
            }else {
                whereSql.append(" and b.FIFFSOP=''"+FSSLX+"''");
            }
        }
        
        //判断出院科室不为空
        if (ConstantsUtils.isNotBank(checkCydetkh)) {
            whereSql.append(" and t.fcytykh in("+checkCydetkh+")");
        }
        
        //出院医生不为空
        if (ConstantsUtils.isNotBank(checkDocbh)) {
            
            String docbh = FDOCLX+"BH";
            //判断为主任医生的话
            if ("FZRDOCTOR".equals(FDOCLX)) {
                docbh = "FZRDOCTBH";
            }
            
            whereSql.append(" and t."+docbh+" in("+checkDocbh+")");
        }
        
        String sql = table+whereSql.toString();
        
        //表示点击查询按钮拼接的sql语句
        if (type == 1) {
            sql = sql.replace("''", "'");
        }
        
        return sql;
    }

    @Override
    public Object confirmSApply(Map<String, Object> map) {
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        boolean b = true;
        /*String insertSql = "INSERT INTO SZH_DGSQ(FUSERCODE,FSHZTBH,FSHZT,FSQRQ,FZYID,FSQMD,FSQDAYS,FREMARK) "*/
        String insertSql = "INSERT INTO SZH_DGSQ(FUSERCODE,FSHZTBH,FSHZT,FSQRQ,FZYID,FSQMD,FSQDAYS,FREMARK,FJYSQR,FTPFL,FTPFLBH) "
                + "VALUES('"+map.get("fcode")+"',0,'未审核','"+aDateFormat.format(date)+"','"+map.get("fzyid")+"',"
                        + ""+map.get("sApplyPurpose")+","+map.get("sBrowseDays")+",'"+map.get("sApplyRemark")+"',"
                                + "'"+map.get("fjysqr")+"','默认',0)";
         
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

	@Override
	public List<Map<String, Object>> getQuerySelfCondition()
			throws Exception {
		String sql="SELECT T.* FROM TQUERYSELFCONDITION T ORDER BY T.FFATHER,T.FPX";
		return this.jdbcTemplate.queryForList(sql);
	}

	@Override
	public void addTemplate(String fmbmc, String fcode, int ftype)
			throws Exception {
		String sql=" IF NOT EXISTS(SELECT 1 FROM dbo.SZH_CXMBMAIN t WHERE t.FMBMC='"+fmbmc+"' AND t.FUSERCODE='"+fcode+"' AND t.FTYPE="+ftype+")  "+
				   " INSERT INTO dbo.SZH_CXMBMAIN (FMBMC,FUSERCODE,FTYPE) VALUES ('"+fmbmc+"','"+fcode+"',"+ftype+")";
		this.jdbcTemplate.execute(sql);
	}

	@Override
	public List<Map<String, Object>> queryConditionGroup(String mbid) {
		String sql="SELECT *,FPID _parentId FROM SZH_CXMBTJZ WHERE FMBID="+mbid+" ORDER BY FPX";
		return this.jdbcTemplate.queryForList(sql);
	}

	@Override
	public void addConditionGroup(String mbid, String pid,
			String tjzmc, String px) throws Exception {
		px=getConditionGroupPX(pid,mbid);
		String sql="INSERT INTO dbo.SZH_CXMBTJZ (FMBID,FPID,FTJZMC,FPX) VALUES ("+mbid+","+pid+",'"+tjzmc+"',"+px+")";
		this.jdbcTemplate.execute(sql);
		//上级id删除fsql
		sql="UPDATE SZH_CXMBTJZ SET FSQL='' WHERE FID="+pid;
		this.jdbcTemplate.execute(sql);
		//删除条件
		sql="DELETE FROM SZH_CXMBTJMX WHERE FZID="+pid;
		this.jdbcTemplate.execute(sql);
	}

	@Override
	public void updateConditionGroup(String id, String tjzmc, String px,String pid,String gx)
			throws Exception {
		String updateFiled="";
		int updCount=0;
		String ypid="";
		if(GlobalFun.hasValue(tjzmc)){
			updateFiled+="FTJZMC='"+tjzmc+"'";
			updCount++;
		}
		if(GlobalFun.hasValue(pid)){
			if(updCount!=0) updateFiled+=" , ";
			updateFiled+="FPID="+pid+"";
			px=getConditionGroupPX(pid,"");
			ypid=this.getConditionGroupPid(id);
			updCount++;
			//上级id删除fsql
			String sql="UPDATE SZH_CXMBTJZ SET FSQL='' WHERE FID="+pid;
			this.jdbcTemplate.execute(sql);
			//删除条件
			sql="DELETE FROM SZH_CXMBTJMX WHERE FZID="+pid;
			this.jdbcTemplate.execute(sql);
		}
		if(GlobalFun.hasValue(px)){
			if(updCount!=0) updateFiled+=" , ";
			updateFiled+="FPX="+px+"";
			updCount++;
		}
		if(GlobalFun.hasValue(gx)){
			if(updCount!=0) updateFiled+=" , ";
			updateFiled+="FGX="+gx+"";
			updCount++;
		}
		
		String sql="UPDATE dbo.SZH_CXMBTJZ SET "+updateFiled+" where fid="+id;
		this.jdbcTemplate.execute(sql);
		if (GlobalFun.hasValue(ypid)) {
			this.resetConditionGroupPX(ypid,"");
		}
		//更新模板SQL
		updateTemplateSQL(getConditionGroupMBid(id));
		/*//将序号为1的关系设为空
		sql="update SZH_CXMBTJZ set fgx='' where fpx=1";
		this.jdbcTemplate.execute(sql);
		//将序号不是1，且关系为空的设为1
		sql="update SZH_CXMBTJZ set fgx=1 where fpx!=1 and fgx is null";
		this.jdbcTemplate.execute(sql);*/
		
	}

	@Override
	public void deleteConditionGroup(String id) throws Exception {
		//获取父级id
		String ypid=getConditionGroupPid(id);
		String mbid2=this.getConditionGroupMBid(id);
		String mbid="";
		if(!GlobalFun.hasValue(ypid) || "null".equals(ypid) || "0".equals(ypid)){
			mbid=this.getConditionGroupMBid(id);
		}
		//删除id并删除所有子节点
		String delSql="DELETE FROM DBO.SZH_CXMBTJZ WHERE FID="+id;
		String ids=getConditionGroupChildrenIds(id,"");
		if(GlobalFun.hasValue(ids)){
			delSql+=" OR FID IN("+ids+")";
			id=id+","+ids;
		}
		this.jdbcTemplate.execute(delSql);
		//删除后重置排序
		this.resetConditionGroupPX(ypid,mbid);
		//删除条件
        String conditionSql="DELETE FROM SZH_CXMBTJMX WHERE FZID in ("+id+")";
        this.jdbcTemplate.execute(conditionSql);
        
        //更新模板SQL
      	updateTemplateSQL(mbid2);
	}
	
	/**
	 * 病案高级查询-条件组-获取排序
	 * @author [lv tang wei]
	 * Describe:
	 * @param pid 父级id
	 * @param table 需要修改的表名
	 * @return
	 * [2017年6月7日]
	 */
	public String getConditionGroupPX(String pid,String mbid){
		String sql="IF EXISTS (SELECT COUNT(FID) PX FROM SZH_CXMBTJZ WHERE FPID="+pid+" GROUP BY FPID) "+
				   "SELECT COUNT(FID)+1 PX FROM SZH_CXMBTJZ WHERE FPID="+pid+" GROUP BY FPID "+
				   "ELSE SELECT 1 PX";
		if(!GlobalFun.hasValue(pid)){//如果是根节点
			sql="IF EXISTS (SELECT COUNT(FID) PX FROM SZH_CXMBTJZ WHERE FMBID="+mbid+" AND (FPID=0 OR FPID IS NULL) GROUP BY FMBID) "+
					   "SELECT COUNT(FID)+1 PX FROM SZH_CXMBTJZ WHERE FMBID="+mbid+" AND (FPID=0 OR FPID IS NULL) GROUP BY FMBID "+
					   "ELSE SELECT 1 PX";
		}
		return this.jdbcTemplate.queryForMap(sql).get("PX").toString();
	}
	
	/**
	 * 病案高级查询-条件组-重置排序
	 * @author [lv tang wei]
	 * Describe:
	 * @param pid 父级id
	 * @param table 需要修改的id
	 * [2017年6月8日]
	 */
	public void resetConditionGroupPX(String pid ,String mbid){
		String sql="SELECT * FROM SZH_CXMBTJZ WHERE FPID="+pid+" ORDER BY FPX";
		String filter=" AND FPID="+pid;
		if(GlobalFun.hasValue(mbid)){//模板下根条件组
			sql="SELECT * FROM SZH_CXMBTJZ WHERE FMBID="+mbid+" AND (FPID IS NULL OR FPID=0) ORDER BY FPX";
			filter=" AND FMBID="+mbid+" AND (FPID IS NULL OR FPID=0) ";
		}
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		for (int i = 0; i < list.size(); i++) {
			Map<String,Object> m=list.get(i);
			sql="UPDATE DBO.SZH_CXMBTJZ SET FPX="+(i+1)+" WHERE FID="+m.get("FID");
			this.jdbcTemplate.execute(sql);
		}
		sql="UPDATE SZH_CXMBTJZ SET FGX=2 WHERE FPX=1"+filter;
		this.jdbcTemplate.execute(sql);
		//将序号不是1，且关系为空的设为1
		sql="UPDATE SZH_CXMBTJZ SET FGX=1 WHERE FPX!=1 AND (FGX IS NULL OR FGX=2)"+filter;
		this.jdbcTemplate.execute(sql);
		
	}
	
	/**
	 * 病案高级查询-条件组-父id
	 * @author [lv tang wei]
	 * Describe:
	 * @param id 
	 * @param table 表名
	 * @return
	 * [2017年6月8日]
	 */
	public String getConditionGroupPid(String id){
		String sql="IF EXISTS (SELECT 1 FROM SZH_CXMBTJZ WHERE FID="+id+") "+
				   " SELECT FPID PID FROM SZH_CXMBTJZ WHERE FID="+id+
				   " ELSE SELECT NULL PID";
		return this.jdbcTemplate.queryForMap(sql).get("PID")+"";
	}
	
	public String getConditionGroupMBid(String id){
		String sql="IF EXISTS (SELECT 1 FROM SZH_CXMBTJZ WHERE FID="+id+") "+
				   " SELECT FMBID MBID FROM SZH_CXMBTJZ WHERE FID="+id+
				   " ELSE SELECT NULL MBID";
		return this.jdbcTemplate.queryForMap(sql).get("MBID")+"";
	}
	/**
	 * 获取全部子节点id
	 * @author [lv tang wei]
	 * Describe:
	 * @param pid
	 * @param table
	 * @param ids
	 * @return
	 * [2017年6月8日]
	 */
	public String getConditionGroupChildrenIds(String pid,String ids){
		String sql="SELECT FID ID FROM SZH_CXMBTJZ WHERE FPID="+pid;
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			for (Map<String, Object> map : list) {
				if(GlobalFun.hasValue(ids)) ids+=",";
				ids+=map.get("ID");
				ids=getConditionGroupChildrenIds(map.get("ID").toString(),ids);
			}
		}
		return ids;
	}

	@Override
	public List<SzhCxmbtjmx> queryCondition(String zid) {
		String sql="SELECT t.* from SZH_CXMBTJMX t,(select ffieldname,fexplan,max(fid) fid FROM SZH_CXMBTJMX b group by ffieldname,fexplan)b WHERE  t.ffieldname = b.ffieldname and t.fexplan = b.fexplan and t.fid = b.fid and t.FZID="+zid+" ORDER BY T.FPX";
		//String sql="SELECT * FROM SZH_CXMBTJMX t WHERE t.FZID="+zid+" ORDER BY T.FPX";
		return this.jdbcTemplate.query(sql, new ObjectMapper(SzhCxmbtjmx.class));
	}

	@Override
	public SzhCxmbtjmx getConditionByCode(String zid,
			String fieldcode) {
		String sql="SELECT * FROM SZH_CXMBTJMX T WHERE T.FZID="+zid+" AND t.FFIELDCODE='"+fieldcode+"' ORDER BY T.FPX";
		List<SzhCxmbtjmx> list=this.jdbcTemplate.query(sql, new ObjectMapper(SzhCxmbtjmx.class));
		if(list.size()>0){
			return list.get(0);
		}
		return null;
	}

	@Override
	public void addCondition(SzhCxmbtjmx mx)
			throws Exception {
		mx.setFsql("("+mx.getFsql()+")");
		mx.setFpx(getConditionPX(mx.getFzid()));
		this.sessionFactory.getCurrentSession().save(mx);
		//更新条件组SQL
		updateConditionGroupSQL(mx.getFzid());
		//更新模板SQL
		updateTemplateSQL(getConditionGroupMBid(mx.getFzid()+""));
	}
	/**
	 * 更新条件组SQL
	 * @author [lv tang wei]
	 * Describe:
	 * @param zid 条件组id
	 * [2017年6月19日]
	 */
	public void updateConditionGroupSQL(int zid)throws Exception{
		//查询条件组下条件
		String sql="SELECT * FROM SZH_CXMBTJMX t WHERE t.FZID="+zid+" ORDER BY t.FPX ";
		List<SzhCxmbtjmx> list=this.jdbcTemplate.query(sql, new ObjectMapper(SzhCxmbtjmx.class));
		StringBuffer fsql=new StringBuffer();
		StringBuffer fsqlzh=new StringBuffer();
		if(list.size()>0){
			fsql.append(" ( ");
			fsqlzh.append("(");
			for (int i = 0; i < list.size(); i++) {
				SzhCxmbtjmx mx=list.get(i);
				if(i!=0){//不是第一个
					if("1".equals(mx.getFgx())){//并且
						fsql.append(" AND ").append(mx.getFsql());
						fsqlzh.append(";并且[").append(mx.getFfieldname()).append("]").append(mx.getFsqlzh());
					}else{
						fsql.append(" OR ").append(mx.getFsql());
						fsqlzh.append(";或者[").append(mx.getFfieldname()).append("]").append(mx.getFsqlzh());
					}
				}else{
					fsql.append(mx.getFsql());
					fsqlzh.append("[").append(mx.getFfieldname()).append("]").append(mx.getFsqlzh());
				}
			}
			fsql.append(" ) ");
			fsqlzh.append(" ) ");
			String updSQL="UPDATE SZH_CXMBTJZ SET FSQL='"+fsql.toString().replaceAll("'", "''")+"',FSQLZH='"+fsqlzh+"' WHERE FID="+zid ;
			this.jdbcTemplate.execute(updSQL);
		}
	}
	
	public int getConditionPX(int zid){
		String sql="IF EXISTS (SELECT COUNT(FID) PX FROM SZH_CXMBTJMX WHERE FZID="+zid+" GROUP BY FZID) "+
				   "SELECT COUNT(FID)+1 PX FROM SZH_CXMBTJMX WHERE FZID="+zid+" GROUP BY FZID "+
				   "ELSE SELECT 1 PX";
		return Integer.parseInt(this.jdbcTemplate.queryForMap(sql).get("PX").toString());
	}
	
	/**
	 * 病案高级查询-条件-重置排序
	 * @author [lv tang wei]
	 * Describe:
	 * @param zid
	 * [2017年6月16日]
	 */
	public void resetConditionPX(String zid){
		String sql="SELECT * FROM SZH_CXMBTJMX WHERE FZID="+zid+" ORDER BY FPX";
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		for (int i = 0; i < list.size(); i++) {
			Map<String,Object> m=list.get(i);
			sql="UPDATE dbo.SZH_CXMBTJMX SET FPX="+(i+1)+" WHERE FID="+m.get("FID");
			this.jdbcTemplate.execute(sql);
		}
		sql="UPDATE SZH_CXMBTJMX SET FGX=2 WHERE FPX=1 AND FZID="+zid;
		this.jdbcTemplate.execute(sql);
		//将序号不是1，且关系为空的设为1
		sql="UPDATE SZH_CXMBTJMX SET FGX=1 WHERE FPX!=1 AND (FGX IS NULL OR FGX=2) AND FZID="+zid;
		this.jdbcTemplate.execute(sql);
		
	}
	
	/**
	 * 病案高级查询-条件-组ID
	 * @author [lv tang wei]
	 * Describe:
	 * @param id 
	 * @param table 表名
	 * @return
	 * [2017年6月8日]
	 */
	public String getConditionZid(String id){
		String sql="IF EXISTS (SELECT 1 FROM SZH_CXMBTJMX WHERE FID="+id+") "+
				   " SELECT FZID ZID FROM SZH_CXMBTJMX WHERE FID="+id+
				   " ELSE SELECT NULL ZID";
		return this.jdbcTemplate.queryForMap(sql).get("ZID")+"";
	}
	
	@Override
	public void updateCondition(SzhCxmbtjmx mx)
			throws Exception {
		StringBuffer updField=new StringBuffer();
		int updCount=0;
		if(GlobalFun.hasValue(mx.getFgx())){
			updField.append("FGX=").append(mx.getFgx());
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFpx()) && 0!=mx.getFpx()){
			if(updCount>0) updField.append(",");
			updField.append("FPX=").append(mx.getFpx());
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFlx())){
			if(updCount>0) updField.append(",");
			updField.append("FLX=").append(mx.getFlx());
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFshownull())){
			if(updCount>0) updField.append(",");
			updField.append("FSHOWNULL=").append(mx.getFshownull());
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFsqlzh())){
			if(updCount>0) updField.append(",");
			updField.append("FSQLZH=").append("'").append(mx.getFsqlzh()).append("'");
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFsql())){
			if(updCount>0) updField.append(",");
			updField.append("FSQL=").append("'(").append(mx.getFsql().replaceAll("'", "''")).append(")'");
			updCount++;
		}
		if(GlobalFun.hasValue(mx.getFexplan())){
			if(updCount>0) updField.append(",");
			updField.append("FEXPLAN=").append("'").append(mx.getFexplan()).append("'");
			updCount++;
		}
		if(updCount>0){
			String sql="UPDATE SZH_CXMBTJMX SET "+updField+" WHERE FID="+mx.getFid();
			this.jdbcTemplate.execute(sql);
			String zid=getConditionZid(mx.getFid()+"");
			//更新条件组SQL
			updateConditionGroupSQL(Integer.parseInt(zid));
			//更新模板SQL
			updateTemplateSQL(getConditionGroupMBid(zid));
		}
		
	}

	@Override
	public void deleteCondition(String id) throws Exception {
		String zid=getConditionZid(id);
		String sql="DELETE FROM SZH_CXMBTJMX WHERE FID="+id;
		this.jdbcTemplate.execute(sql);
		//重置排序
		resetConditionPX(zid);
		//更新条件组SQL
		updateConditionGroupSQL(Integer.parseInt(zid));
		//更新模板SQL
		updateTemplateSQL(getConditionGroupMBid(zid));
	}

	/**
	 * 更新模板SQL
	 * @author [lv tang wei]
	 * Describe:
	 * @param mbid
	 * [2017年6月19日]
	 */
	public void updateTemplateSQL(String mbid){
		//查根条件组
		String queryTjz="SELECT * FROM SZH_CXMBTJZ t WHERE t.FMBID="+mbid+" AND (t.FPID IS NULL OR t.FPID=0) ORDER BY t.FPX";
		List<Map<String,Object>> tjzList=this.jdbcTemplate.queryForList(queryTjz);
		StringBuffer tempSQL=new StringBuffer();
		StringBuffer tempSQLZH=new StringBuffer();
		int notEmptySQL=0;
		for (int i = 0; i < tjzList.size(); i++) {
			Map<String,Object> tjz=tjzList.get(i);
			String groupSQL=getConditionGroupChildrensSQL(tjz.get("FID").toString(),"");
			String groupSQLZH=getConditionGroupChildrensSQLZH(tjz.get("FID").toString(),"");
			//去除括号后判断是否有条件
			if (ConstantsUtils.isNotBank(groupSQL.replaceAll("\\(|\\)","").trim()) || (!ConstantsUtils.isNotBank(groupSQL.replaceAll("\\(|\\)", "").trim()) && ConstantsUtils.isNotBank(tjz.get("FSQL")+""))) {
				groupSQL=GlobalFun.hasValue(groupSQL)?groupSQL:tjz.get("FSQL").toString();
				groupSQLZH=GlobalFun.hasValue(groupSQLZH)?groupSQLZH:tjz.get("FSQLZH")+"";
				if(i!=0){
					if("1".equals(tjz.get("FGX"))){
						if(notEmptySQL>0){
							tempSQL.append(" AND ");
							tempSQLZH.append(";并且");
						}
						tempSQL.append(groupSQL);
						tempSQLZH.append(groupSQLZH);
					}else{
						if(notEmptySQL>0){
							tempSQL.append(" OR ");
							tempSQLZH.append(";或者");
						}
						tempSQL.append(groupSQL);
						tempSQLZH.append(groupSQLZH);
					}
				}else{
					tempSQL.append(" ").append(groupSQL);
					tempSQLZH.append(groupSQLZH);
				}
				notEmptySQL++;
			}
		}
		String updSQL="UPDATE SZH_CXMBMAIN SET FSQL='"+tempSQL.toString().replaceAll("'", "''")+"',FSQLZH='"+tempSQLZH+"' WHERE FID="+mbid;
		this.jdbcTemplate.execute(updSQL);
	}
	
	public String getConditionGroupChildrensSQL(String pid,String groupSQL){
		String sql="SELECT * FROM SZH_CXMBTJZ WHERE FPID="+pid;
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> map=list.get(i);
				//是否有SQL语句
				if(GlobalFun.hasValue(map.get("FSQL"))){
					if(i!=0){//不是第一个就加上关系
						if("1".equals(map.get("FGX"))){
							groupSQL+=" AND "+map.get("FSQL");
						}else{
							groupSQL+=" OR "+map.get("FSQL");
						}
					}else{//第一个不需要加关系
						groupSQL+=" "+map.get("FSQL");
					}
				}
				groupSQL=getConditionGroupChildrensSQL(map.get("FID").toString(),groupSQL);
			}
			if(GlobalFun.hasValue(groupSQL.trim())){
				groupSQL="("+groupSQL+")";
			}
		}
		return groupSQL;
	}
	
	public String getConditionGroupChildrensSQLZH(String pid,String groupSQLZH){
		String sql="SELECT * FROM SZH_CXMBTJZ WHERE FPID="+pid;
		List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			for (int i = 0; i < list.size(); i++) {
				Map<String,Object> map=list.get(i);
				//是否有SQL语句
				if(GlobalFun.hasValue(map.get("FSQLZH"))){
					if(i!=0){//不是第一个就加上关系
						if("1".equals(map.get("FGX"))){
							groupSQLZH+=";并且"+map.get("FSQLZH");
						}else{
							groupSQLZH+=";或者"+map.get("FSQLZH");
						}
					}else{//第一个不需要加关系
						groupSQLZH+=""+map.get("FSQLZH");
					}
				}
				groupSQLZH=getConditionGroupChildrensSQL(map.get("FID").toString(),groupSQLZH);
			}
			if(GlobalFun.hasValue(groupSQLZH.trim())){
				groupSQLZH="("+groupSQLZH+")";
			}
		}
		return groupSQLZH;
	}
	
	@Override
	public Map<String,Object> getTemplateByID(String mbid) throws Exception {
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT * FROM ");
		//sql.append(" TPATIENTVISIT t,");/*住院信息表(主表)*/
		sql.append(" SZH_BASE t");/*住院信息表(主表)*/
		/*sql.append(" TDIAGNOSE t1,");诊断表
		sql.append(" TOPERATION t2,");手术表
		sql.append(" TBABYCARD t3,");妇婴卡
		sql.append(" TKNUBCARD t4,");肿瘤卡
		sql.append(" TCHADD t5,");中医附加
		sql.append(" TDIAGNOSEADD t6");诊断附加*/
		String fsql=getTemplateSQL(mbid);
		if (ConstantsUtils.isNotBank(fsql)) {
			sql.append(getInnerTableByWhereSQL(fsql));
			sql.append(" WHERE 1=1 AND ("+fsql+")");
		}
		this.jdbcTemplate.execute(sql.toString());
		String querySQL="select * from dbo.szh_cxmbmain where fid="+mbid;
		Map<String,Object> map= this.jdbcTemplate.queryForMap(querySQL);
		map.put("SQL", sql.toString());
		return map;
	}
	
	public String getTemplateSQL(String mbid){
		String sql="SELECT * FROM SZH_CXMBMAIN WHERE FID="+mbid;
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			return (String) list.get(0).get("FSQL");
		}
		return null;
	}
	
	public String getTemplateSQLZH(String mbid){
		String sql="SELECT * FROM SZH_CXMBMAIN WHERE FID="+mbid;
		List<Map<String,Object>> list=this.jdbcTemplate.queryForList(sql);
		if(list.size()>0){
			return (String) list.get(0).get("FSQLZH");
		}
		return null;
	}
    @Override
    public Object confirmBApply(Map<String, Object> map) {
        String fsqtjwz = (String) map.get("fsqtjwz");
        String fsqtjsql =  map.get("fsqtjsql")==null?"":(String) map.get("fsqtjsql");
        String ftype =  map.get("ftype")==null?"1":(String) map.get("ftype");
        fsqtjwz = fsqtjwz.substring(0, fsqtjwz.length()-1);
        
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        boolean b = true;
        //调用返回sql语句方法
        String sql = fsqtjsql.replaceAll("'", "''");
        if("1".equals(ftype)){//病案高级查询
        	sql=returnQuerySql(map, 0, 1).replace("'", "''");
        }
        String insertSql = "insert into SZH_PLSQ(FUSERCODE,FSQMD,FSQDAYS,FSQTJWZ,FREMARK,FSQTJSQL,FSQRQ,FSHZTBH,FSHZT,FJYSQR,FTYPE) VALUES"
                + "('"+map.get("fcode")+"',"+map.get("bApplyPurpose")+","+map.get("bBrowseDays")+","
                + "'"+fsqtjwz+"','"+map.get("bApplyRemark")+"','"+sql+"','"+aDateFormat.format(date)+"',0,'未审核','"+map.get("fjysqr")+"','"+ftype+"')";
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public PageHelper checkPurposeDataByUser(Map<String, Object> map) {
        
        Tsysuser userObject = (Tsysuser) map.get("user");      //当前用户信息
        
        String dateType = (String) map.get("dateType");        //日期类型(1 申请日期  2出院日期)
        String startDate = (String) map.get("startDate");      //起始日期时间
        String endDate = (String) map.get("endDate");          //结束日期时间
        String fjymd = (String) map.get("fjymd");              //借阅目的id
        
        String fspzt = (String) map.get("fspzt");              //审批状态(1:全部,2:中间流程,3:批准,4:驳回)
        String isApproval = (String) map.get("isApproval");    //是否已经审批(true 需要审批的  false 包括需要审批和已经审批过的)
        String scheckNum = (String) map.get("scheckNum");      //病案号、条码号或住院号id
        String scheckFname = (String) map.get("scheckFname");  //患者姓名
        String scheckFsqr = (String) map.get("scheckFsqr");    //申请人
        
        int page = Integer.parseInt((String) map.get("page"));
        int rows = Integer.parseInt((String) map.get("rows"));
        
        String fields = "q.fid,q.fjlid,q.fqsrq,x.fname fsqmd,z.fname,z.fprn,z.ftimes,a.fname flc,z.fcydate,q.fzyid,z.fcytykh,m.FSPKS,q.fspy,x.fid fjymdid,"
                + "q.fspms,q.fspday,q.FSPDAYUNIT,q.fspzt,q.FDQLC fdqlc,q.fxjlc,q.fsqrq,q.fjysqr,q.fsqdays,q.fsprq,q.fremark,q.fjsrq,q.ftpfl fsqtp ";
        
        String table = " (select CASE WHEN c.fqsrq is null then t.fsqrq ELSE c.fqsrq END fqsrq,c.FDQLC,"
                       +" t.fsqmd,c.fspy,c.fspms,CASE WHEN c.fspday IS null THEN t.fsqdays else c.fspday END fspday,t.fid,t.fsqdays,c.fsprq,t.fremark,t.ftpfl,"
                       +" CASE WHEN c.FSPDAYUNIT IS NULL then 1 ELSE c.FSPDAYUNIT end FSPDAYUNIT,c.fspzt,t.fsqrq,t.fjysqr,c.FJSRQ,c.fid fjlid,"
                       +" t.fzyid,(case when fxjlc is null then (select top 1 fpx from SZH_SPJB where fjymdid=t.fsqmd "
                       +" ORDER BY fpx ) else (SELECT TOP 1 fpx FROM SZH_SPJB WHERE fpx>(SELECT FPX from SZH_SPJB WHERE FNAME = c.FDQLC) and FJYMDID="+fjymd+" ORDER BY fpx) end) fxjlc  from (select * from SZH_DGSQ b LEFT JOIN "
                       +" (select max(FSPRQ) FSPRQ,FSQID from SZH_DGSQJL GROUP BY FSQID)a ON a.fsqid=b.fid) t"
                       +" LEFT JOIN SZH_DGSQJL c on t.fid=c.FSQID and t.FSPRQ=c.FSPRQ) q INNER JOIN SZH_JYMD x ON q.fsqmd = x.FID "
                       +" INNER JOIN SZH_BASE z ON q.fzyid = z.fzyid left JOIN SZH_SPJBRY m ON q.fxjlc = m.FSPJBID "
                       +" left JOIN SZH_SPJB a ON q.fxjlc = a.FPX";
        
        String where = "";
        //判断是否检测需要审批的病案
        if ("false".equals(isApproval)) {
            where = " or m.fusercode is null";
        }
        
        StringBuffer sBuffer = new StringBuffer(" (m.FUSERCODE = '"+userObject.getFcode()+"' "+where+")");
        
        if (!"".equals(where)) {
            sBuffer.append(" and q.fspy='"+userObject.getFname()+"'");
        }
        
        //判断起始日期是否为空
        if (ConstantsUtils.isNotBank(startDate)) {
            if ("1".equals(dateType)) {
                sBuffer.append(" and q.fsqrq >= '"+startDate+"'");
            }else {
                sBuffer.append(" and z.fcydate >= '"+startDate+"'");
            }
        }
        
        //判断结束日期是否为空
        if (ConstantsUtils.isNotBank(endDate)) {
            if ("1".equals(dateType)) {
                sBuffer.append(" and dateadd(day,-1,z.fcydate)<'" + endDate + "'");
            }else {
                sBuffer.append(" and dateadd(day,-1,z.fcydate)<'" + endDate + "'");
            }
        }
        
        //判断借阅目的id是否为空
        if (ConstantsUtils.isNotBank(fjymd)) {
            sBuffer.append(" and x.fid = "+fjymd+"");
        }
        
        //判断审批状态值
        if (!"全部".equals(fspzt)) {
            sBuffer.append(" and q.fspzt = '"+fspzt+"'");
        }
        
        //判断病案号是否为空
        if (ConstantsUtils.isNotBank(scheckNum)) {
            sBuffer.append(" and z.fprn = "+scheckNum+"");
        }
        
        //判断患者姓名值是否为空
        if (ConstantsUtils.isNotBank(scheckFname)) {
            sBuffer.append(" and z.fname = "+scheckFname+"");
        }
        
        //判断申请人是否为空
        if (ConstantsUtils.isNotBank(scheckFsqr)) {
            sBuffer.append(" and q.fjysqr = '"+scheckFsqr+"'");
        }
        PageHelper pageHelper = this.queryMssqlSplitPageByProc(fields, table, sBuffer.toString(), "q.fsqrq", 1, "q.fsqrq", page, rows);
        return pageHelper;
    }

    @Override
    public Object checkNextExaminationPerson(Map<String, Object> map) {
        
        List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
        //检测下级审批级别是否为结束
        String queryJbSql = "SELECT TOP 1 * FROM SZH_SPJB WHERE FJYMDID="+map.get("fjymdid")+" AND FPX>"+map.get("fdqspjb")+" ORDER BY fpx";
        list = this.jdbcTemplate.queryForList(queryJbSql);
        
        //判断如果下级审批级别不存在  既当前审批级别为结束审批级别
        if (list.size() == 0) {
            return false;
        }else {
            String queryRySql = "SELECT TOP 1 s.*,b.* FROM SZH_SPJB b LEFT JOIN SZH_SPJBRY s ON b.FPX = s.FSPJBID "
                    + "WHERE FJYMDID="+map.get("fjymdid")+" AND FPX>"+map.get("fdqspjb")+" ORDER BY fpx";
            list = this.jdbcTemplate.queryForList(queryRySql);
        }
        return list;
    }
    /**
     * 病案浏览审批审批通过方法
     */
    @Override
    public Object submitsApprovalMedicol(Map<String, Object> map) {
      //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        int fsqid = Integer.parseInt((String) map.get("fsqid"));  //申请id
        String fqsrq = (String) map.get("fqsrq");    //起始日期
        String fspms = (String) map.get("fspms");    //审批描述
        
        int fspday = Integer.parseInt((String) map.get("fspday"));  //浏览审批数值
        int fspdayunit = Integer.parseInt((String) map.get("fspdayunit"));  //浏览审批数值单位
        
        String fspzt = (String) map.get("fspzt");   //审批状态
        String fdqlc = (String) map.get("fdqlc");   //当前流程
        String fxjlc = (String) map.get("fxjlc");   //下级流程
        
        Tsysuser user = (Tsysuser) map.get("user");  //获取当前用户
        
        String queryfjsrq = "SELECT dbo.GetEndDate('"+fqsrq+"',"+fspday+","+fspdayunit+")";//获取结束日期
        String fjsrq = this.jdbcTemplate.queryForObject(queryfjsrq, String.class);
        
        boolean b = true;
        String insertSql = "INSERT INTO SZH_DGSQJL([FSQID], [FQSRQ], [FJSRQ], [FSPY], [FSPMS], [FSPDAY], [FSPDAYUNIT], [FSPZT], [FDQLC], [FXJLC], "
                          +"[FSPRQ]) VALUES ("+fsqid+", '"+fqsrq+"', '"+fjsrq+"', '"+user.getFname()+"', '"+fspms+"', "
                          +""+fspday+", "+fspdayunit+", '"+fspzt+"', '"+fdqlc+"', '"+fxjlc+"', '"+aDateFormat.format(date)+"')";
        
        //判断如果为审批为批准  执行
        if ("批准".equals(fspzt)) {
            String updateSql = "update SZH_DGSQ set fshztbh =1,fshzt='批准' where fid="+fsqid;
            jdbcTemplate.execute(updateSql);
        }else if ("驳回".equals(fspzt)) {
            String updateSql = "update SZH_DGSQ set fshztbh =2,fshzt='驳回' where fid="+fsqid;
            jdbcTemplate.execute(updateSql);
        }
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object submitsNotApprovalMedicol(Map<String, Object> map) {
        int fjlid = Integer.parseInt((String)map.get("fjlid"));
        String fxjlc = (String) map.get("fxjlc");
        Tsysuser user = (Tsysuser) map.get("user");
        String fspms = (String) map.get("fspms");
        
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        String querySql = "select * from SZH_DGSQJL where fid="+fjlid;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        
        //查找当前流程的上级流程信息
        String queryCurrentSpjb = "select top 1 * from SZH_SPJB where fpx<"+fxjlc;
        List<Map<String, Object>> list2 = this.jdbcTemplate.queryForList(queryCurrentSpjb);
        
        boolean b = true;
        String insertSql = "INSERT INTO SZH_DGSQJL([FSQID], [FQSRQ], [FJSRQ], [FSPY], [FSPMS], [FSPDAY], [FSPDAYUNIT], [FSPZT], [FDQLC], [FXJLC], "
                          +"[FSPRQ]) VALUES ("+list.get(0).get("FSQID")+", '"+list.get(0).get("FQSRQ")+"', '"+list.get(0).get("FJSRQ")+"', "
                          + "'"+user.getFname()+"', '"+fspms+"', "+list.get(0).get("FSPDAY")+", "+list.get(0).get("FSPDAYUNIT")+", "
                          + "'中间流程', '"+list2.get(0).get("FNAME")+"', '"+list2.get(0).get("FPX")+"', '"+aDateFormat.format(date)+"')";
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
        
    }

    @Override
    public PageHelper checkPurposeDataBatch(Map<String, Object> map) {
        Tsysuser userObject = (Tsysuser) map.get("user");      //当前用户信息
        
        String startDate = (String) map.get("startDate");      //起始日期时间
        String endDate = (String) map.get("endDate");          //结束日期时间
        String fjymd = (String) map.get("fjymd");              //借阅目的id
        
        String scheckFsqr = (String) map.get("scheckFsqr");    //申请人
        
        int page = Integer.parseInt((String) map.get("page"));
        int rows = Integer.parseInt((String) map.get("rows"));
        
        String fields = "q.fid,q.fjlid,q.fqsrq,x.fname fsqmd,a.fname flc,m.FSPKS,q.fspy,x.fid fjymdid,q.fsqtjwz,"
                + "q.fspms,q.fspday,q.FSPDAYUNIT,q.fspzt,q.FDQLC fdqlc,q.fxjlc,q.fsqrq,q.fjysqr,q.fsqdays,q.fsprq,q.fremark,q.fjsrq ";
        
        String table = " (select CASE WHEN c.fqsrq is null then t.fsqrq ELSE c.fqsrq END fqsrq,c.FDQLC,"
                       +" t.fsqmd,c.fspy,c.fspms,CASE WHEN c.fspday IS null THEN t.fsqdays else c.fspday END fspday,t.fid,t.fsqdays,c.fsprq,t.fremark,"
                       +" CASE WHEN c.FSPDAYUNIT IS NULL then 1 ELSE c.FSPDAYUNIT end FSPDAYUNIT,c.fspzt,t.fsqrq,t.fjysqr,c.FJSRQ,c.fid fjlid,t.fsqtjwz,"
                       +" (case when c.FDQLC is null then (select top 1 fpx from SZH_SPJB where fjymdid=t.fsqmd "
                       +" ORDER BY fpx ) else (SELECT TOP 1 fpx FROM SZH_SPJB WHERE fpx>(SELECT FPX from SZH_SPJB WHERE FNAME = c.FDQLC) and FJYMDID="+fjymd+" ORDER BY fpx) end) fxjlc  from (select * from SZH_PLSQ b LEFT JOIN "
                       +" (select max(FSPRQ) FSPRQ,FSQID from SZH_PLSQJL GROUP BY FSQID)a ON a.fsqid=b.fid) t"
                       +" LEFT JOIN SZH_PLSQJL c on t.fid=c.FSQID and t.FSPRQ=c.FSPRQ) q INNER JOIN SZH_JYMD x ON q.fsqmd = x.FID "
                       +" left JOIN SZH_SPJBRY m ON q.fxjlc = m.FSPJBID "
                       +" left JOIN SZH_SPJB a ON q.fxjlc = a.FPX";
        
        StringBuffer sBuffer = new StringBuffer(" (m.FUSERCODE = '"+userObject.getFcode()+"' OR m.FUSERCODE IS NULL)");
        
        //判断起始日期是否为空
        if (ConstantsUtils.isNotBank(startDate)) {
             sBuffer.append(" and q.fsqrq >= '"+startDate+"'");
        }
        
        //判断结束日期是否为空
        if (ConstantsUtils.isNotBank(endDate)) {
             sBuffer.append(" and dateadd(day,-1,q.fsqrq)<'" + endDate + "'");
        }
        
        //判断借阅目的id是否为空
        if (ConstantsUtils.isNotBank(fjymd)) {
            sBuffer.append(" and x.fid = "+fjymd+"");
        }
        
        //判断申请人是否为空
        if (ConstantsUtils.isNotBank(scheckFsqr)) {
            sBuffer.append(" and q.fjysqr = '"+scheckFsqr+"'");
        }
        PageHelper pageHelper = this.queryMssqlSplitPageByProc(fields, table, sBuffer.toString(), "q.fsqrq", 1, "q.fsqrq", page, rows);
        return pageHelper;
    }

    @Override
    public Object submitbApprovalMedicol(Map<String, Object> map) {
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        int fsqid = Integer.parseInt((String) map.get("fsqid"));  //申请id
        String fqsrq = (String) map.get("fqsrq");    //起始日期
        String fspms = (String) map.get("fspms");    //审批描述
        
        int fspday = Integer.parseInt((String) map.get("fspday"));  //浏览审批数值
        int fspdayunit = Integer.parseInt((String) map.get("fspdayunit"));  //浏览审批数值单位
        
        String fspzt = (String) map.get("fspzt");   //审批状态
        String fdqlc = (String) map.get("fdqlc");   //当前流程
        String fxjlc = (String) map.get("fxjlc");   //下级流程
        
        Tsysuser user = (Tsysuser) map.get("user");  //获取当前用户
        
        String queryfjsrq = "SELECT dbo.GetEndDate('"+fqsrq+"',"+fspday+","+fspdayunit+")";//获取结束日期
        String fjsrq = this.jdbcTemplate.queryForObject(queryfjsrq, String.class);
        
        boolean b = true;
        String insertSql = "INSERT INTO SZH_PLSQJL([FSQID], [FQSRQ], [FJSRQ], [FSPY], [FSPMS], [FSPDAY], [FSPDAYUNIT], [FSPZT], [FDQLC], [FXJLC], "
                          +"[FSPRQ]) VALUES ("+fsqid+", '"+fqsrq+"', '"+fjsrq+"', '"+user.getFname()+"', '"+fspms+"', "
                          +""+fspday+", "+fspdayunit+", '"+fspzt+"', '"+fdqlc+"', '"+fxjlc+"', '"+aDateFormat.format(date)+"')";
        
        //判断如果为审批为批准  执行
        if ("批准".equals(fspzt)) {
            String updateSql = "update SZH_PLSQ set fshztbh =1,fshzt='批准' where fid="+fsqid;
            jdbcTemplate.execute(updateSql);
        }else if ("驳回".equals(fspzt)) {
            String updateSql = "update SZH_PLSQ set fshztbh =2,fshzt='驳回' where fid="+fsqid;
            jdbcTemplate.execute(updateSql);
        }
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object submitbNotApprovalMedicol(Map<String, Object> map) {
        int fjlid = Integer.parseInt((String)map.get("fjlid"));
        String fxjlc = (String) map.get("fxjlc");
        Tsysuser user = (Tsysuser) map.get("user");
        String fspms = (String) map.get("fspms");
        
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        String querySql = "select * from SZH_PLSQJL where fid="+fjlid;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        
        //查找当前流程的上级流程信息
        String queryCurrentSpjb = "select top 1 * from SZH_SPJB where fpx<"+fxjlc;
        List<Map<String, Object>> list2 = this.jdbcTemplate.queryForList(queryCurrentSpjb);
        
        boolean b = true;
        String insertSql = "INSERT INTO SZH_PLSQJL([FSQID], [FQSRQ], [FJSRQ], [FSPY], [FSPMS], [FSPDAY], [FSPDAYUNIT], [FSPZT], [FDQLC], [FXJLC], "
                          +"[FSPRQ]) VALUES ("+list.get(0).get("FSQID")+", '"+list.get(0).get("FQSRQ")+"', '"+list.get(0).get("FJSRQ")+"', "
                          + "'"+user.getFname()+"', '"+fspms+"', "+list.get(0).get("FSPDAY")+", "+list.get(0).get("FSPDAYUNIT")+", "
                          + "'中间流程', '"+list2.get(0).get("FNAME")+"', '"+list2.get(0).get("FPX")+"', '"+aDateFormat.format(date)+"')";
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    }

    @Override
    public Object checkUserHaveJuris(Map<String, Object> map) {
        String fcode = (String) map.get("fcode"); //用户工号
        int fpx = Integer.parseInt((String)map.get("fdqspjb"));  //当前审批级别
        
        String querySql = "SELECT p.* FROM SZH_SPJB s INNER JOIN SZH_SPJBRY p ON s.FPX = p.FSPJBID WHERE s.FPX="+fpx+" AND p.FUSERCODE='"+fcode+"'";
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }

	@Override
	public Map<String, Object> medicalAdvancedInquiryBatchBrowseApplyQuery(
			Map<String, Object> map) throws Exception {
		int page=Integer.parseInt(map.get("page")+"");
		int rows=Integer.parseInt(map.get("rows")+"");
		StringBuffer sql=new StringBuffer();
		String check="";
		String fcode=(String) map.get("fcode");
		 //住院id拼接字符串
        String zyids = "";
        String ztSQL="";
        //表示执行查询的方法
    	int qxtype=1;
    	//病案浏览权限  1.无权限  每份病历都需要申请  2.本科权限  除本科室外，所有的病历需要申请  3.全部权限  不需要申请
    	String qxsql="SELECT MAX(FLLQXTYPE) qxtype FROM SZH_GROUPLLQXINFO WHERE FGROUPCODE IN (SELECT FGROUPCODE FROM TSYSUSER WHERE FCODE='"+fcode+"')";
    	try{  
    		Map<String,Object> qx=this.jdbcTemplate.queryForMap(qxsql);
    		qxtype=(Integer) qx.get("qxtype");
        }catch (EmptyResultDataAccessException e) {  
        	qxtype=1;//没有设置默认无权限
        }
    	//获取当前日期
        String date = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
        //获取用户姓名
        String fname = (String) map.get("fname");
        //list存放具有浏览权限的病案住院id
        List<Map<String, Object>> list = null;
        //查看单个申请和图片分类申请对比当前日期比较判断 病案是否具有浏览权限
        String queryBapply = "SELECT a.FZYID FROM SZH_DGSQ a INNER JOIN(SELECT FSQID,FJSRQ FROM SZH_DGSQJL "
                + "WHERE FSPZT='批准' and fjsrq>'"+date+"') b ON a.FID = b.FSQID and a.fusercode = '"+fcode+"'";
        list = jdbcTemplate.queryForList(queryBapply);
        //遍历mblist数组 将模板id放入 arra数组中
        for (int i = 0; i < list.size(); i++) {
            zyids += list.get(i).get("FZYID") + ",";
        }
        //查看批量申请批准，病案是否具有浏览权限
        String querybDefault = "SELECT * FROM SZH_PLSQJL WHERE FSPZT='批准' AND FJSRQ>'"+date+"'";
        List<Map<String, Object>> listabDefalut = jdbcTemplate.queryForList(querybDefault);
        //根据当前日期与批量申请 批准后的结束日期判断已经存在批量申请批准的记录
        if (listabDefalut.size()>0) {
            for (int i = 0; i < listabDefalut.size(); i++) {
                //根据住院id 判断该病案是否在批量申请  sql语句中
                String queryInfoByZyid = "SELECT fsqtjsql FROM SZH_PLSQ WHERE FID = "+listabDefalut.get(i).get("FSQID")+" "
                        + "AND FJYSQR = '"+fname+"' ";
                //得到查询sql语句
                String plsqCxSql = jdbcTemplate.queryForObject(queryInfoByZyid, String.class);
                //将每个查询语句查询出的结果放置
                List<Map<String, Object>> resuList = jdbcTemplate.queryForList(plsqCxSql);
                if (resuList.size() > 0) {
                    //循环将住院id放入字符串中
                    for (int j = 0; j < resuList.size(); j++) {
                        zyids += resuList.get(i).get("FZYID");
                    }
                }
            }
        }
       
        check = zyids.length()>1?zyids.substring(0, zyids.length()-1):zyids;
    	if(1==qxtype){//1.无权限  每份病历都需要申请 
            ztSQL="CASE when  t.fzyid IN("+check+") THEN 2 WHEN t.fzyid IN(select fzyid FROM SZH_SHOOTRECORD) THEN 1 else 0 END fzt ";
    	}else if(2==qxtype){// 2.本科权限  除本科室外，所有的病历需要申请  可浏览：本院已翻拍、本院已归档+申请的  
    		ztSQL="CASE when  t.fzyid IN("+check+") OR t.FZYID IN (SELECT FZYID FROM SZH_SHOOTRECORD WHERE FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FRYTYKH IN (SELECT FTYKH FROM TSYSUSER WHERE FCODE='"+fcode+"'))) OR t.FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FRYTYKH IN (SELECT FTYKH FROM TSYSUSER WHERE FCODE='"+fcode+"'))) THEN 2 WHEN t.fzyid IN(select fzyid FROM SZH_SHOOTRECORD) THEN 1 else 0 END fzt ";
    	}else if(3==qxtype){//3.全部权限  不需要申请(可浏览：已归档、已翻拍)
    		ztSQL="CASE WHEN t.FZYID IN (SELECT FZYID FROM SZH_SHOOTRECORD) OR t.FZYID IN (SELECT FZYID FROM SZH_BAGD WHERE FSTATUS=1) THEN 2 else 0 END fzt ";
    	}
        
		//CASE WHEN t.FZYID IN ( SELECT fzyid FROM SZH_DGSQ WHERE FUSERCODE = '"+map.get("fcode")+"' AND FSHZTBH = 1) THEN 2 WHEN t.FZYID IN (SELECT FZYID FROM SZH_SHOOTRECORD) THEN 1 ELSE 0 END fzt
		sql.append("SELECT DISTINCT t.*,"+ztSQL+"  FROM ");
		//sql.append(" TPATIENTVISIT t,");/*住院信息表(主表)*/
		sql.append(" SZH_BASE t ");/*住院信息表(主表)*/
		String fsql=getTemplateSQL(map.get("mbid")+"");
		if (ConstantsUtils.isNotBank(fsql)) {
			sql.append(getInnerTableByWhereSQL(fsql));
			sql.append(" WHERE 1=1 AND ("+fsql+")");
		}
		int total=this.jdbcTemplate.queryForList("select * from ("+sql.toString()+") s1 where s1.fzt!=0").size();
		//String fysql="SELECT * FROM (SELECT TOP "+rows+" * FROM (SELECT TOP "+(rows*page)+" * FROM ("+sql.toString()+") s1 where s1.fzt!=0 ORDER BY s1.fcydate DESC) s2 ORDER BY s2.fcydate asc) s3 ORDER BY s3.fcydate DESC";
		String fysql="SELECT * FROM (SELECT TOP "+rows+" * FROM (SELECT TOP "+(rows*page)+" * FROM ("+sql.toString()+") s1 where 1=1 ORDER BY s1.fcydate DESC) s2 ORDER BY s2.fcydate asc) s3 ORDER BY s3.fcydate DESC";
		list=this.jdbcTemplate.queryForList(fysql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 根据条件sql获取关联表
	 * @author [lv tang wei]
	 * Describe:
	 * @param wheresql
	 * @return
	 * [2017年6月28日]
	 */
	public String getInnerTableByWhereSQL(String wheresql){
		StringBuffer innerTable=new StringBuffer();
		if(wheresql.toLowerCase().indexOf("t1.")!=-1){
			innerTable.append(" INNER JOIN TDIAGNOSE T1 ON T.FPRN = T1.FPRN AND T.FTIMES = T1.FTIMES ");/*诊断表*/
		}
		if(wheresql.toLowerCase().indexOf("t2.")!=-1){
			innerTable.append(" INNER JOIN TOPERATION T2 ON T.FPRN = T2.FPRN AND T.FTIMES = T2.FTIMES ");/*手术表*/
		}
		if(wheresql.toLowerCase().indexOf("t3.")!=-1){
			innerTable.append(" INNER JOIN TBABYCARD T3 ON T.FPRN = T3.FPRN AND T.FTIMES = T3.FTIMES ");/*妇婴卡*/
		}
		if(wheresql.toLowerCase().indexOf("t4.")!=-1){
			innerTable.append(" INNER JOIN TKNUBCARD T4 ON T.FPRN = T4.FPRN AND T.FTIMES = T4.FTIMES ");/*肿瘤卡*/
		}
		if(wheresql.toLowerCase().indexOf("t5.")!=-1){
			innerTable.append(" INNER JOIN TCHADD T5 ON T.FPRN = T5.FPRN AND T.FTIMES = T5.FTIMES ");/*中医附加*/
		}
		if(wheresql.toLowerCase().indexOf("t6.")!=-1){
			innerTable.append(" INNER JOIN TDIAGNOSEADD T6 ON T.FPRN = T6.FPRN AND T.FTIMES = T6.FTIMES ");/*诊断附加*/
		}
		return innerTable.toString();
	}

    @Override
    public List<Map<String, Object>> getListOptions(Map<String, Object> maps) {
        int id = Integer.parseInt((String)maps.get("id"));
        String date = (String) maps.get("date");
        String fzyid = (String) maps.get("fzyid");
        Tsysuser user = (Tsysuser) maps.get("user");
        
        //定义返回的模板数据
        List<Map<String, Object>> mblist = null;
        
        int qxtype=1;
    	//病案浏览权限  1.无权限  每份病历都需要申请  2.本科权限  除本科室外，所有的病历需要申请  3.全部权限  不需要申请
    	String sql="SELECT MAX(FLLQXTYPE) qxtype FROM SZH_GROUPLLQXINFO WHERE FGROUPCODE IN (SELECT FGROUPCODE FROM TSYSUSER WHERE FCODE='"+user.getFcode()+"')";
    	try{  
    		Map<String,Object> qx=this.jdbcTemplate.queryForMap(sql);
    		qxtype=(Integer) qx.get("qxtype");
        }catch (EmptyResultDataAccessException e) {  
        	qxtype=1;//没有设置默认无权限
        }
    	
    	if(qxtype == 3){
    		//检索个人默认模板
            String queryUserMb = "SELECT c.* FROM SZH_IMGMBMAIN a INNER JOIN SZH_IMGMBDETAIL b ON a.FID = b.FIMGMBID "
                    + "INNER JOIN SZH_BLLXMX c ON b.FMBID = c.FID "
                    + "WHERE FGROUPCODE = '"+user.getFcode()+"' AND "
                    + "FISDEFAULT=0 AND FSYSFLAG = 1";
            mblist = jdbcTemplate.queryForList(queryUserMb);
            
            //判断当前用户默认模板为空加载系统模板
            if (mblist.size() == 0) {
                
              //检索系统默认模板
                String querySysMb = "SELECT c.*,1 isJuris FROM SZH_IMGMBMAIN a INNER JOIN SZH_IMGMBDETAIL b ON a.FID = b.FIMGMBID "
                        + "INNER JOIN SZH_BLLXMX c ON b.FMBID = c.FID INNER JOIN TSYSUSER t ON a.FGROUPCODE = t.FGROUPCODE "
                        + "WHERE t.fcode = '"+user.getFcode()+"' AND"
                        +" a.FISDEFAULT=0 AND a.FSYSFLAG = 0";
                
                mblist = jdbcTemplate.queryForList(querySysMb);
            }
            //直接返回list
            return mblist;
    	}
        
        //首先查看单个申请病案的结束日期 判断该用户是有具有查看默认模板的权限
        String queryaDefault = "SELECT b.* FROM SZH_DGSQ a INNER JOIN SZH_DGSQJL b ON a.FID = b.fsqid where a.fshztbh=1"
                               +" and b.fsprq=(SELECT MAX(fsprq) from SZH_DGSQJL where fsqid = "+id+") AND b.fjsrq>'"+date+"'";
        
        List<Map<String, Object>> listaDefalut = jdbcTemplate.queryForList(queryaDefault);
        
        Boolean b = false;  //默认该病案在批量申请中不存在权限可以访问默认图片
        
        //查看批量申请的结束日期 判断该用户是有具有查看默认模板的权限
        String querybDefault = "SELECT * FROM SZH_PLSQJL WHERE FSPZT='批准' AND FJSRQ>'"+date+"'";
        
        List<Map<String, Object>> listabDefalut = jdbcTemplate.queryForList(querybDefault);
        
        //根据当前日期与批量申请 批准后的结束日期判断已经存在批量申请批准的记录
        if (listabDefalut.size()>0) {
            
            for (int i = 0; i < listabDefalut.size(); i++) {
                
                //根据住院id 判断该病案是否在批量申请  sql语句中
                String queryInfoByZyid = "SELECT fsqtjsql FROM SZH_PLSQ WHERE FID = "+listabDefalut.get(i).get("FSQID")+" "
                        + "AND FJYSQR = '"+user.getFname()+"' ";
                
                //得到查询sql语句
                String plsqCxSql = jdbcTemplate.queryForObject(queryInfoByZyid, String.class);
                
                //根据查询sql语句 判断该住院id是否在当中
                String query = plsqCxSql + " and t.fzyid='"+fzyid+"'";
                
                //判断存在
                if (jdbcTemplate.queryForList(query).size() > 0) {
                    b = true;
                    break;
                }
            }
        }
        
        //判断如果病案申请存在批准记录且日期
        if (listaDefalut.size() > 0 || b) {
            
            //检索个人默认模板
            String queryUserMb = "SELECT c.* FROM SZH_IMGMBMAIN a INNER JOIN SZH_IMGMBDETAIL b ON a.FID = b.FIMGMBID "
                    + "INNER JOIN SZH_BLLXMX c ON b.FMBID = c.FID "
                    + "WHERE FGROUPCODE = '"+user.getFcode()+"' AND "
                    + "FISDEFAULT=0 AND FSYSFLAG = 1";
            mblist = jdbcTemplate.queryForList(queryUserMb);
            
            //判断当前用户默认模板为空加载系统模板
            if (mblist.size() == 0) {
                
              //检索系统默认模板
                String querySysMb = "SELECT c.*,1 isJuris FROM SZH_IMGMBMAIN a INNER JOIN SZH_IMGMBDETAIL b ON a.FID = b.FIMGMBID "
                        + "INNER JOIN SZH_BLLXMX c ON b.FMBID = c.FID INNER JOIN TSYSUSER t ON a.FGROUPCODE = t.FGROUPCODE "
                        + "WHERE t.fcode = '"+user.getFcode()+"' AND"
                        +" a.FISDEFAULT=0 AND a.FSYSFLAG = 0";
                
                mblist = jdbcTemplate.queryForList(querySysMb);
            }
            
            //直接返回list
            return mblist;
        }
        
        //查看该病案图片分类单个申请(且审核通过的记录)
        String queryImgSql = "SELECT * FROM SZH_DGSQ WHERE fzyid = '"+fzyid+"' AND fjysqr='"+user.getFname()+"' AND ftpflbh <>0 AND fshztbh=1";
        List<Map<String, Object>> imglist = jdbcTemplate.queryForList(queryImgSql);
        
        if (imglist.size()>0) {
            //遍历每个批准的图片分类模板
            for (int i = 0; i < imglist.size(); i++) {
                
                //查询该申请的图片分类是否具有权限
                String imgcx = "SELECT a.ftpflbh fid FROM SZH_DGSQ a INNER JOIN SZH_DGSQJL b ON a.FID = b.fsqid where a.fshztbh=1"
                        +" and b.fsprq=(SELECT MAX(fsprq) from SZH_DGSQJL where fsqid = "+imglist.get(i).get("fid")+") AND b.fjsrq>'"+date+"'";
                
                //将具有权限的模板放入mblist中
                mblist.add(jdbcTemplate.queryForList(imgcx).get(0));
            }
        }
        String ids = "";
        if(mblist != null ){
        	//遍历mblist数组 将模板id放入 arra数组中
            for (int i = 0; i < mblist.size(); i++) {
                ids += mblist.get(i).get("fid") + ",";
            }
        }
        
        //根据模板具有权限的模板id 判断哪些模板具有权限，最终返回 mblist
        String getmbByid = "SELECT *,CASE WHEN FID in("+ids.substring(0, ids.length()-1)+") THEN 1 ELSE 2 END isJuris FROM SZH_BLLXMX";
        mblist = jdbcTemplate.queryForList(getmbByid.toString());
        
        return mblist;
    }

    @Override
    public Object confirmSApplyImg(Map<String, Object> map) {
        //获取当前日期
        Date date = new Date();
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        boolean b = true;
        String insertSql = "INSERT INTO SZH_DGSQ(FUSERCODE,FSHZTBH,FSHZT,FSQRQ,FZYID,FSQMD,FSQDAYS,FREMARK,FJYSQR,FTPFL,FTPFLBH) "
                + "VALUES('"+map.get("fcode")+"',0,'未审核','"+aDateFormat.format(date)+"','"+map.get("fzyid")+"',"
                        + ""+map.get("sApplyPurpose")+","+map.get("sBrowseDays")+",'"+map.get("sApplyRemark")+"',"
                                + "'"+map.get("fjysqr")+"','"+map.get("imgname")+"',"+map.get("imgid")+")";
        
        try {
            jdbcTemplate.execute(insertSql);
        } catch (Exception e) {
            b = false;
        }
        return b;
    } 
    
    @Override
    public Object loadFieldsByTempFilterId(String mbID) {
        String querySql = "select FID, FFIELDNAME title,ALIGN align,WIDTH width,FNAME fname from SZH_FIELDMBDETAIL where FFIELDMBID="+mbID;
        List<Map<String, Object>> list = this.jdbcTemplate.queryForList(querySql);
        return list;
    }
    
    /**
     * 病案错误报告处理  查询
     */
    @Override
	public PageHelper queryAllErrossb(Map<String, Object> map) {
    	String rqlx = (String) map.get("rqlx");
		String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2"); 
		String cwlx = (String) map.get("cwlx");
		String sfcl = (String) map.get("sfcl"); 
		
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;
		if("1".equals(rqlx)){
			if(ConstantsUtils.isNotBank(date1)){
				sb.append("and b.FCYDATE >= '"+date1+"'"); //出院日期
			}
			if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and b.FCYDATE < '"+Date+"'");
			}
		}else if("2".equals(rqlx)){
			if(ConstantsUtils.isNotBank(date1)){
				sb.append(" and a.FSBDATE >= '"+date1+"'");  //上报日期
			}
			if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and a.FSBDATE < '"+Date+"'");
			}
		}
		
		if(ConstantsUtils.isNotBank(cwlx)){//错误类型
			if(cwlx.equals("0")){
				sb.append(" ");
			}else{
				sb.append(" and a.FOPTYPE = '"+ cwlx +"' ");
			}
		} 
	 
		if(ConstantsUtils.isNotBank(sfcl)){ //处理状态 0:未处理 1:已处理
			if(sfcl.equals("0")){
				sb.append(" ");
			}else{ 
				sb.append(" and a.FSTATE = '"+ sfcl +"' ");
			}
		} 
	   
		if (ConstantsUtils.isNotBank(page)) {
            currentPage = Integer.valueOf(page);
        }
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }    
		 
		String field = " a.FID,a.FBARCODE,a.FFLFZBH,a.FTPBH,a.FSBUSER,a.FSBDATE,a.FOPTYPE,a.FREASON,a.FSTATE,a.FCLUSER,a.FCLYJ,a.FCLDATE,a.FPAGECODE,a.FZYID,a.FREMARK "
				+ ",b.FNAME,b.FSEX,b.FAGE,b.FCYDATE,b.FCYDEPT ";
		String table = " SZH_ERRORSB a LEFT JOIN SZH_BASE b on a.FTPBH  =b.FPRN ";
		return this.queryMssqlSplitPageByProc(field, table, sb.toString(), " a.FZYID ", 1, " a.FZYID ", currentPage, pageSize);
	}
    
    
    /**
     * 病案错误报告处理   处理状态修改 
     */
    @Override
    public Object submitFstate(Map<String, Object> map) {
        //获取当前日期
        Date date = new Date();   //当前时间
        SimpleDateFormat aDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateNow =aDateFormat.format(date);
        int FID = Integer.parseInt((String) map.get("FID"));  //错误id 
        String FSTATE = (String) map.get("FSTATE");    //处理状态
        String FCLYJ = (String) map.get("FCLYJ");     //处理意见   
        Tsysuser user = (Tsysuser) map.get("user");  //获取当前用户
       
        boolean b = true;
        //1=已处理 ,3=拒绝
        String updateSql = "update szh_errorsb set FSTATE ='"+ FSTATE +"',FCLYJ='" + FCLYJ +"',FCLUSER='"+user.getFname()+"',FCLDATE='"+ aDateFormat.format(date)+ "' where FID="+FID;
       
        try {
        	 jdbcTemplate.execute(updateSql); 
        } catch (Exception e) {
            b = false;
        }
        
        return b;
    }
    
    /**
     * 人员浏览统计 统计
     */  
    @Override
    public PageHelper SumMedicalStatistical(Map<String, Object> map) { 
			String date1 = (String) map.get("date1");
			String date2 = (String) map.get("date2"); 
			String FUSERNAME = (String) map.get("FUSERNAME");   
			String FUSERCODE = (String) map.get("FUSERCODE");  
			
			String page = (String) map.get("page");
			String rows = (String) map.get("rows");
			
			StringBuffer sb = new StringBuffer(" 1=1 ");
			int pageSize = 10;
			int currentPage = 1;
			 
			if(ConstantsUtils.isNotBank(date1)){
				sb.append("and a.FDATE >= '"+date1+"'"); //浏览日期
			}
			if(ConstantsUtils.isNotBank(date2)){
				Date DATE = new Date();
				try {
					DATE = sdf.parse(date2);
				} catch (ParseException e) {
					e.printStackTrace();
				}
	    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
	    		 String Date = sdf.format(date);
				sb.append(" and a.FDATE < '"+Date+"'");
			}
			 
			
			if(ConstantsUtils.isNotBank(FUSERNAME)){//浏览人
				if(FUSERNAME.equals("0")){
					sb.append(" ");
				}else{
					sb.append(" and a.FUSERNAME = '"+ FUSERNAME +"' ");
				}
			} 
			/*if(ConstantsUtils.isNotBank(FUSERCODE)){//浏览人编号
				if(FUSERCODE.equals("0")){
					sb.append(" ");
				}else{
					sb.append(" and a.FUSERCODE = '"+ FUSERCODE +"' ");
				}
			}*/ 
		   
			if (ConstantsUtils.isNotBank(page)) {}
	            currentPage = Integer.valueOf(page);
	        
	        if (ConstantsUtils.isNotBank(rows)) {
	            pageSize = Integer.valueOf(rows);
	        }    
			 
			String field = " a.FUSERNAME,a.sumFTimes ";
			String table = " (select a.FUSERNAME,sum(a.Ftimes) sumFTimes from  szh_browserecord a where "+sb.toString() +" GROUP BY a.FUSERNAME) a ";
			return this.queryMssqlSplitPageByProc(field, table, "", " a.FUSERNAME ", 1, " a.FUSERNAME ", currentPage, pageSize);
	}
    
    /**
     * 人员浏览统计 详情
     */
    @Override
	public PageHelper MbrowseInfo(Map<String, Object> map) {
    	String date1 = (String) map.get("date1");
		String date2 = (String) map.get("date2"); 
		String FUSERNAME = (String) map.get("FUSERNAME");     
		
		String page = (String) map.get("page");
		String rows = (String) map.get("rows");
		
		StringBuffer sb = new StringBuffer(" 1=1 ");
		int pageSize = 10;
		int currentPage = 1;  
		
		if(ConstantsUtils.isNotBank(date1)){
			sb.append("and a.FDATE >= '"+date1+"'"); //浏览日期
		}
		if(ConstantsUtils.isNotBank(date2)){
			Date DATE = new Date();
			try {
				DATE = sdf.parse(date2);
			} catch (ParseException e) {
				e.printStackTrace();
			}
    		 Date date = new Date(DATE.getTime() + 1000 * 60 * 60 * 24);
    		 String Date = sdf.format(date);
			sb.append(" and a.FDATE < '"+Date+"'");
		}
		
		if(ConstantsUtils.isNotBank(FUSERNAME)){//浏览人
			if(FUSERNAME.equals("0")){
				sb.append(" ");
			}else{
				sb.append(" and a.FUSERNAME = '"+ FUSERNAME +"' ");
			}
		}  
	   
		if (ConstantsUtils.isNotBank(page)) {}
            currentPage = Integer.valueOf(page);
        
        if (ConstantsUtils.isNotBank(rows)) {
            pageSize = Integer.valueOf(rows);
        }    
		 
		String field = " a.FUSERNAME,a.FDATE,sumFTimes ";
		String table = " (select a.FUSERNAME,a.FDATE,sum(a.FTIMES) as sumFTimes from szh_browserecord a where "+sb.toString() +" GROUP BY a.FUSERNAME,a.FDATE ) a ";
		return this.queryMssqlSplitPageByProc(field, table, "", " a.FDATE ", 1, " a.FDATE ", currentPage, pageSize);
	}
     
}
